
/* **THIS PROGRAM reads in the appended call report data created in bh2020-sub-01a-createCallData.do 
(see master.do file for additional information on this step) 

**Running the code as we did requires first having the relevant non-public call report data on non-commercial banks.

Abbreviations used below:
FBO - Foreign Banking Organization
BHC - Bank Holding Company
CB - Commercial Bank
SSB - State Savings Bank
FSB - Federal Savings Bank

*/

local int2020_dir <set your filepaths here>
local out2020_dir <set your filepaths here>

cd "`int2020_dir'"
use bh-01a-callAppended,clear

/*
Note:
	Depending on the source of data, the (bank/entity) id and date variable names might vary, but they all capture the same information.
	Some examples of the naming conventions you might see are:
	 -bank-level id variable:  rssd9001 or  id_rssd or entity
	 -quarterly date variable: date8 or  date_8 or d_dt or rssd9999]
*/


*MANAGE DATE VARIABLES
tostring date_8, replace
gen dated 	= date(date_8,"YMD")
assert !missing(dated)


gen year 	= year(dated)
gen q 		= quarter(dated)
gen dateq	= yq(year, q)

format dated 	%td
format dateq 	%tq


gen    dateq2 = qofd(date_sas)
assert dateq2== dateq,fast

drop dateq2 date_sas date_8 rssd9999



*ADJUST Top Holder ID (parent bank ID) for banks that are foreign banking organizations [FBO] when necessary  (top holder ID [rssd9348] is missing)
	*Group FBO by foreign parent bank if
		*foreign owned AND rssd9348 is missing or zero   
replace rssd9348=rssd9360 	if inlist(rssd9348,0,.) & inlist(rssd9360,0,.)!=1


*If institution is its own Top Holder, fill Top Holder var (rssd9348) with entity-id, so that BHC aggregations reflect the same institution
replace rssd9348=entity 	if rssd9348==0


*===================SEVERAL CUTS / SUBSETS======================
*DROP shell banks---ones with idle/anomalous assets (see bhreadme.docx for more details)

	drop if inlist(entity,775456, 927518 ,955007, 1228623, 972406)  	

	drop if inlist(entity,167565,3357620)  								


*DROP certain types of banks or banks with certain characteristics

	drop if rcfd2170==. | rcfd2170==0 						//drops banks with missing or zero assets 
	drop if rcon9804 < 41 					 				//keep only filers of certain FFIEC forms - see 'label define ld_rcon9804' below
	keep if inlist(rssd9048,200,300)						//keeps commercial banks and ALL savings banks (FSBs removed in the next line)  			
	drop if rssd9048==300 & rssd9421==1						//drops FSBs  								
	keep if rssd9425==0 									//drops nonstandard banks--generally nonlenders like credit card banks 					
	drop if inlist(rssd9200,"VI","AS","FM","GU","MH") 		//drops banks in US territories										
	drop if inlist(rssd9200,"MP","PW","UM","XX","PR")												
	drop if rssd9950==0  									//drops "new banks" that retained a previous bank charter on their "Opening Date" 
	

*DROP denovos banks - 1st 2 years after an opening date (see main text for more details)

	tostring rssd9950,gen(open_date_str)
	gen open_date = date(open_date_str,"YMD")
	gen open_dateq = qofd(open_date)
		format open_dateq %tq
	gen denovo_diff = dateq-open_dateq
	drop if denovo_diff<8  		

	
	
	
*DROP NonLenders, Additional: 
	***The variable rssd9425 (used above) identifies some anomalous banks and 'nonlending' institutions.
	***In this step, we also categorize a bank as a nonlender if its average loans (over its entire 'life') 
	***are less than 10% of its average total assets (over its entire 'life').

	local miss_list "rcfnb574 rcfd1410 rcfd1246  rcfd1249 rcfd1247 rcfd1250 rcfd1423 rcfd1422 rcfd1423 rcfnb573 rcfnb573 rcfd1407 rcfd1403 rcfd2008 rcon2008 rcfdb538 rcfd2123 rcfd2122 rcfn2200 rcfd1410 rcon3259 rcfd3210 rcfd2170 rcfd2143 rcfd3164 rcfd3163 rcfd3165 rcfd5506 rcfd5507 rcfdb026 rcfd8274 rcfda223 rcon1410 rcon0296" 
	foreach x of local miss_list {
		replace `x'=0 if `x'==. 
	} 

	gen double totlns 					= (rcfd2123 + rcfd2122)
	bys entity: egen double avg_totlns 	= mean(totlns)
	bys entity: egen double avg_assets 	= mean(rcfd2170)

	gen double lendmean	= (avg_totlns/avg_assets)*100  
		la var lendmean "(Avg Tot Loans / Avg Tot Assets)*100"
		
	drop if lendmean<=10 


*DROP Credit Card Banks, Additional:
	***The variable rssd9425 (used above) identifies some anomalous banks and 'nonlending' institutions, like credit card banks.
	***In this step, we also categorize a bank as a credit card bank if its credit card loans EVER exceed 50% of its total loans.

	gen double cc_lns 	= .
	replace cc_lns 		= rcfd2008 if dateq>=tq(1984q1) & dateq<=tq(2000q4)
	replace cc_lns 		= rcfdb538 if dateq>=tq(2001q1)

	gen double 	cc_share = (cc_lns/totlns)*100 
		la var 	cc_share "(Total Credit Card Loans / Tot Loans)*100"
	replace 	cc_share = 0 if cc_share==.

	gen 	remove_cc_obs = 0
	replace remove_cc_obs 					= 1 if cc_share>50	
	bys entity: egen remove_cc_bnk 			= max(remove_cc_obs) 			
	drop if remove_cc_bnk==1   	
					

		
					
*DROP Last 2 Qs of data of banks that voluntarily liquidate (vol liq)
	gen 	vol_liq_flag = 1 if rssd9061==1
	replace vol_liq_flag = 0 if vol_liq_flag==.
	
	bys entity: egen  vol_liq_flag_ent 	= max(vol_liq_flag)  			//flag the whole entity if ever a vol liq
	bys entity: egen  lastq 			= max(dateq) 			
	format lastq %tq 
	
	gen 	final2q_flag 	= 1 if lastq==dateq 						//flag the entity's last quarter in data
	replace final2q_flag 	= 1 if lastq-1==dateq & final2q_flag==. 	//flag the entity's penultimate quarter in data
	
	gen rm_fin2q_vol_liq 	= 1 if final2q_flag==1 & vol_liq_flag_ent==1
	
	sort entity dateq

	drop if rm_fin2q_vol_liq==1 

	
********************************************************************************
****GEN BANK FINANCIAL MEASURES - Must define financial variables over time based on "rcon9804" -- data come from forms filed by banks of different types.
	/*Notes regarding sources of "rcon9804" data:
		Banks file different forms depending first, on the presence of foreign offices, and second, by asset size.
		
			-FFIEC 031 Reporters both domestic & foreign offices.
			-FFIEC 032 - Domestic Offices Only and Total Assets of $300 Million or More
			-FFIEC 033 - Domestic Offices Only and Total Assets of $100 Million or More But Less Than $300 Million
			-FFIEC 034 - Domestic Offices Only and Total Assets of Less Than $100 Million
			
			--In 2001, the asset threshold was done away with and form 041 (Bank with Domestic Offices Only),
			consolidated forms 032,033,034 into one.
			--In 2017, an asset threshold was reinstated and the 051 form started (Banks with Domestic Offices Only and Total Assets Less than $5 Billion)
				However, banks meeting this threshold had a choice of forms to file.
				"An institution eligible to file the FFIEC 051 report (as discussed below) may choose instead to file the FFIEC 041 report."
				For more info: 
					https://www.ffiec.gov/pdf/FFIEC_forms/FFIEC031_FFIEC041_201912_i.pdf
					Some blank report forms:
					https://www.ffiec.gov/forms051.htm - Banks less than $5 Billion in Assets
					https://www.ffiec.gov/forms041.htm - Banks w/ Domestic Offices Only
					Call Report Data Dictionary located: https://www.federalreserve.gov/apps/mdrm/data-dictionary*/
	
			*Careful! rcon9804 codes the 031 filers as "51" and the new 051 filers as "59"
			
			#delimit ;
				label define ld_rcon9804 
				41 "FFIEC 041 Reporter (effective 3/31/01)"
				51 "FFIEC 031 Reporter"
				52 "FFIEC 032 Reporter (effective 3/31/84, discontinued after 12/31/00)"
				53 "FFIEC 033 Reporter (effective 3/31/84, discontinued after 12/31/00)"
				54 "FFIEC 034 Reporter (effective 3/31/84, discontinued after 12/31/00)"
				59 "FFIEC 051 Report (effective 3/31/17)"
				;
			#delimit cr

			label values rcon9804 ld_rcon9804

			

********************************************************************************



*-------------CALCULATE TOTAL NON-PERFORMING LOANS--------------	
*Loans Past-Due as a share of total loans

/*Note:
	Here, we include foreign activity where applicable (variables for 031 form filers, and starting with mnemonic "rcfd").
	Foreign activity cannot be backed out of the RENPL variables (rcfd1422/1423) prior to 1987q1. 
	Thus, we maintain consistency with earlier years and add foreign activity back in to later periods when domestic vs. foreign line items 
	were made available - (rcfd1250/1249, rcfnb573/b574).
	
	This had two benefits for the analysis:
	1) We were able to extend the time series and maintain consistency over time.
	2) The main utility of these variables is as an exogenous (to a given domestic location) measure of bank health since the bank's
	operations are primarily overseas
*/
	
gen double npl90	= 0
gen double nplnon  	= 0


*NPLS - past due 90+ days
replace npl90 	= rcfd1407

*NPLS - nonaccrual
replace nplnon 	= rcfd1403 

	
*--------------REAL ESTATE NON-PERFORMING LOANS--------------
gen double re90		= 0
gen double renon 	= 0

	*================================================================================
	***rcon9804==51 | rcon9804==52 
	*Banks with Foreign Offices AND/OR over $300 Million in assets
	*================================================================================

		*RE - past due 90+ days
		replace re90 	= (rcfd1422) 			if inlist(rcon9804,51,52) 	&  dateq<tq(1987q1) 						/*cannot remove activity from foreign branches from rcfd1422*/ 
		replace re90 	= (rcfd1246 + rcfd1249) if inlist(rcon9804,51,52) 	& (dateq>=tq(1987q1) & dateq<=tq(2000q4)) 	/*adds back in activity from foreign branches to make time series consistent - see note above*/ 
		replace re90 	= (rcfd1422 + rcfnb573) if inlist(rcon9804,51) 		&  dateq>=tq(2001q1) 						/*adds back in activity from foreign branches to make time series consistent - see note above*/ 

		*RE - nonaccrual
		replace renon 	= (rcfd1423) 			if inlist(rcon9804,51,52) 	&  dateq<tq(1987q1)							/*cannot remove activity from foreign branches from rcfd1423*/ 
		replace renon 	= (rcfd1247 + rcfd1250) if inlist(rcon9804,51,52) 	& (dateq>=tq(1987q1) & dateq<=tq(2000q4)) 	/*adds back in activity from foreign branches to make time series consistent - see note above*/ 
		replace renon 	= (rcfd1423 + rcfnb574) if inlist(rcon9804,51) 		&  dateq>=tq(2001q1)						/*adds back in activity from foreign branches to make time series consistent - see note above*/ 
		
	
	*================================================================================	
	***rcon9804==53 | rcon9804==54 | rcon9804==41  | rcon9804==59  
	*Banks with Domestic Offices Only
	*================================================================================	
	
		*RE - past due 90+ days
		replace re90 	= (rcon1211) 			if inlist(rcon9804,53,54) 	&  dateq<=tq(2000q4)	
		replace re90 	= (rcfd1422) 			if inlist(rcon9804,41,59) 	&  dateq>=tq(2001q1)	

		*RE - nonaccrual
		replace renon 	= (rcon1212)			if inlist(rcon9804,53,54) 	&  dateq<=tq(2000q4)
		replace renon 	= (rcfd1423) 			if inlist(rcon9804,41,59) 	&  dateq>=tq(2001q1)




*SUM COMPONENTS
gen double npls  	= npl90 + nplnon
gen double renpls 	= re90  + renon


drop if npls==0 & inlist(renpls,0,.)!=1		//Correct some misreporting - if renpls==nonmissing/nonzero, npls should not be 0.



*################################ RATIOS #########################################
*NONPERFORMING LOAN RATIO
gen double npl_ratio 	= .

*REAL ESTATE NONPERFORMING LOAN RATIO
gen double renpl_ratio 	= . 


replace renpl_ratio = 0 					if renpls==0 	& rcfd1410==0
replace renpl_ratio = (renpls/rcfd1410)*100 if rcfd1410!=. 	& rcfd1410!=0  //rcfd1410=="Total Loans Secured by Real Estate"

/*
Address 034 filers (rcon9804==54) who do not report re-npls before 1985q2 
"Why?"
We need to sum location deposits WITHOUT these entities for the renpl measures.
NPLs and other measures are available so we need to have two sets of location data, (which then can be used to create two sets of BH measures):
1) One for all measures that contain all banks 
2) One for RENPL measures that exclude 034 form filers - (Banks with rcon9804==54) 
	-This latter set will generally be referred to with the suffix "_refix"
	
*/

replace renpl_ratio	=. 						if rcon9804==54 & dateq<=tq(1985q1)
la var  renpl_ratio "(renpls/rcfd1410)*100"


replace npl_ratio	= 0 					if npls==0 		& totlns==0
replace npl_ratio 	= (npls/totlns)*100 	if totlns!=. 	& totlns!=0
la var  npl_ratio "(npls/totlns)*100"

assert npl_ratio!=.,fast


*-------------------------------------------------------------------------------
*NONPERFORMING ASSET RATIO 2 (npAsset ratio) = (npl / Assets)
*Ratios relative to assets as opposed to total loans 

gen double 	npa_ratio_2 	= 0 						if npls==0 | rcfd2170==0
replace 	npa_ratio_2		= (npls/rcfd2170)*100   	if npa_ratio_2!=0 	
	la var 	npa_ratio_2 		"(npls/assets)*100"
assert npa_ratio_2!=.,fast	
*-------------------------------------------------------------------------------

*-------------------------------------------------------------------------------		 
*REAL ESTATE NONPERFORMING ASSET RATIO 2   = (renpl / Assets)
gen double 	renpa_ratio_2 	= 0 						if renpls==0 | rcfd2170==0
replace 	renpa_ratio_2 	= (renpls/rcfd2170)*100		if renpa_ratio_2!=0 					
	la var 	renpa_ratio_2 		"(renpls/assets)*100"
gen 	renpa2_check = 1 	if renpa_ratio_2==. & dateq>=tq(1985q2)
assert 	renpa2_check!=1,fast
drop 	renpa2_check
*-------------------------------------------------------------------------------


*################################ SAVE DATA #########################################


cd "`out2020_dir'"

		*##########################################################
		save bh-01b-call,replace  					
		*##########################################################



		

